<?php

namespace app\controllers\action;

class Teacher
{
    /**
     * 获取全部学生信息
     * 额外参数student_name如果有student_name则只获取一个学生名字
     * @param $conn \mysqli
     * @param $student_name string
     * @return array
     */
    public static function get_student_info(&$conn, $student_name)
    {
        $return = [];
        if (empty($student_name)) {
            $stmt = $conn->prepare("SELECT * FROM students");
            $stmt->execute();
            $result = $stmt->get_result();
            while ($row = ($result->fetch_assoc())) {
                unset($row["password"]);
                $return[] = $row;
            }
            $result->free_result();
            $stmt->close();
        } else {
            $stmt = $conn->prepare("SELECT * FROM students where student_name LIKE concat('%',?,'%')");
            $stmt->bind_param("s", $student_name);
            $stmt->execute();
            $result = $stmt->get_result();
            while ($row = ($result->fetch_assoc())) {
                unset($row["password"]);
                $return[] = $row;
            }
            $result->free_result();
            $stmt->close();
        }

        return $return;
    }

    /**
     * 获取学生成绩
     * 如果使用student_name参数则查询某个同学的成绩
     * 如果使用min,max参数则查某个区间内的同学成绩
     * student_name和min max参数不可混用
     * @param $conn \mysqli
     * @param $student_name string
     * @param $min string
     * @param $max string
     * @return array
     */
    public static function get_student_grade(&$conn, $student_name, $min, $max)
    {
        $return = [];
        if (empty($student_name)) {
            if (!isset($min) || !isset($max)) {
                $stmt = $conn->prepare("SELECT * FROM student_grades ORDER BY total desc");
            } else {
                $stmt = $conn->prepare("SELECT * FROM student_grades where `student_grades`.`total`>=? AND `student_grades`.`total`<=? ORDER BY total desc");
                $stmt->bind_param("ii", $min, $max);
            }
        } else {
            $stmt = $conn->prepare("SELECT * FROM student_grades where student_grades.student_name LIKE concat('%',?,'%')");
            $stmt->bind_param("s", $student_name);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = ($result->fetch_assoc())) {
            $return[] = $row;
        }
        $result->free_result();
        $stmt->close();

        return $return;
    }

    /**
     * 教师增加一名学生
     * @param $conn \mysqli
     * @param $student_id string
     * @param $student_name string
     * @param $student_sex string
     * @return bool
     */
    public static function add_student(&$conn, $student_id, $student_name, $student_sex)
    {
        if (!isset($student_id) || !isset($student_name) || !isset($student_sex))
            return false;
        $code = false;
        $conn->autocommit(false);
        $conn->begin_transaction();
        $stmt1 = $conn->prepare("INSERT INTO `students`(`student_id`,`student_name`, `student_sex`) VALUES(?,?,?)");
        $stmt1->bind_param("ssi", $student_id, $student_name, $student_sex);
        // $stmt1->execute();
        $stmt2 = $conn->prepare("INSERT INTO `grades`(`student_id`) VALUES(?)");
        $stmt2->bind_param("s", $student_id);
        if ($stmt1->execute() && $stmt2->execute()) {
            $code = true;
            $conn->commit();
        } else {
            $conn->rollback();
        }
        $stmt1->close();
        $stmt2->close();
        return $code;
    }

    /**
     * 删除一名学生
     * @param $conn \mysqli
     * @param $student_id string
     * @return bool
     */
    public static function delete_student(&$conn, $student_id)
    {
        $code = false;
        $stmt = $conn->prepare("DELETE FROM `students` WHERE `student_id`=?");
        $stmt->bind_param("s", $student_id);
        $stmt->execute();
        if ($stmt->affected_rows > 0) {
            $code = true;
        }
        $stmt->close();
        return $code;
    }

    /**
     * 更新学生成绩
     * @param $conn \mysqli
     * @param $student_id string
     * @param $attendance string
     * @param $homework string
     * @param $quiz string
     * @param $interactive string
     * @param $midterm string
     * @param $final string
     * @return bool
     */
    public static function update_student_grade(&$conn, $student_id, $attendance, $homework, $quiz, $interactive, $midterm, $final)
    {
        $code = false;
        $stmt = $conn->prepare("update grades set `attendance`=?,`homework`=?,`quiz`=?,`interactive`=?,`midterm`=?,`final`=? where `student_id`=?");
        $stmt->bind_param("dddddds", $attendance, $homework, $quiz, $interactive, $midterm, $final, $student_id);
        $stmt->execute();
        if ($stmt->affected_rows > 0) {
            $code = true;
        }
        $stmt->close();
        return $code;
    }

    /**
     * 更新学生信息
     * @param $conn \mysqli
     * @param $student_id string
     * @param $student_name string
     * @param $student_sex string
     * @param $description string
     * @return bool
     */
    public static function update_student_info(&$conn, $student_id, $student_name, $student_sex, $description)
    {
        $code = false;
        $stmt = $conn->prepare("update students set `student_name`=?,`student_sex`=?,`description`=? where `student_id`=?");
        $stmt->bind_param("ssss", $student_name, $student_sex, $description, $student_id);
        $stmt->execute();
        if ($stmt->affected_rows > 0) {
            $code = true;
        }
        $stmt->close();
        return $code;
    }

    /**
     * 获取审核信息
     * @param \mysqli $conn
     * @return array
     */
    public static function get_verify(&$conn)
    {
        $return = [];
        $stmt = $conn->prepare("SELECT * FROM `teachers` where is_check=0");
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = ($result->fetch_assoc())) {
            unset($row["password"]);
            $return[] = $row;
        }
        $result->free_result();
        $stmt->close();

        return $return;
    }

    /**
     * 审核 同意
     * @param $conn \mysqli
     * @return bool
     */
    public static function agree_verify(&$conn, $user_id)
    {
        $code = false;
        $stmt = $conn->prepare("update `teachers` set is_check=1 where `teacher_id`=? and is_check=0");
        $stmt->bind_param("s", $user_id);
        $stmt->execute();
        if($stmt->affected_rows > 0) {
            $code = true;
        }
        $stmt->close();
        return $code;
    }

    /**
     * 审核 拒绝
     * @param $conn \mysqli
     * @return bool
     */
    public static function degree_verify(&$conn, $user_id)
    {
        $code = false;
        $conn->autocommit(false);
        $conn->begin_transaction();
        $stmt2 = $conn->prepare("delete from `teachers` where `teacher_id`=? and is_check=0");
        $stmt2->bind_param("s", $user_id);
        $stmt2->execute();
        if($stmt2->affected_rows > 0) {
            $code = true;
            $conn->commit();
        } else {
            $conn->rollback();
        }
        $stmt2->close();
        return $code;
    }

}